<?php

include_once "src/controller/dbmanagement/Connection.php";
include_once "src/model/Service.php";
include_once "src/model/ProvidedService.php";
include_once "src/model/State.php";
include_once "src/model/Consultant.php";
include_once "src/model/SearchResult.php";
include_once "src/regexception/DBConnectionException.php";
include_once "src/regexception/DBException.php";
include_once "src/regexception/GeneralRegistryException.php";

class DBManager {

    private static function genRandomString ($length = 5) {
        $characters = 'abcdefghijklmnopqrstuvwxyz ';
        $string = '';

        for ($p = 0; $p < $length; $p++) {
            $string .= $characters[mt_rand(0, strlen($characters))];
        }

        return $string;
    }

    public static function populateRegistry () {
        try {
            $cnn = new Connection();
            $sql  = "INSERT INTO CONSULTANT SET ";
            $sql .= "First_Name = '" . self::genRandomString() . "', ";
            $sql .= "Last_Name = '" . self::genRandomString() . "', ";
            $sql .= "Email = '" . self::genRandomString(10) . "@" . self::genRandomString() . ".com"  . "', ";
            $sql .= "Password = '" . self::genRandomString() . "', ";
            $sql .= "Profile_First_Name = '" . self::genRandomString() . "', ";
            $sql .= "Profile_Last_Name = '" . self::genRandomString() . "', ";
            $sql .= "Company_Name = '" . self::genRandomString() . "', ";
            $sql .= "Physical_Address = '" . self::genRandomString() . "', ";
            $sql .= "Physical_City = '" . self::genRandomString() . "', ";

            $sql .= "Physical_State_Id = '44', ";

            $sql .= "Physical_Zip_Code = '78666', ";

            $sql .= "Physical_Plus_Four = '1234', ";

            $sql .= "Mailing_Address = '" . self::genRandomString() . "', ";
            $sql .= "Mailing_City = '" . self::genRandomString() . "', ";

            $sql .= "Mailing_State_Id = '5', ";

            $sql .= "Mailing_Zip_Code = '12345', ";

            $sql .= "Mailing_Plus_Four = '1234', ";

            $sql .= "Profile_Email = '" . self::genRandomString() . "@" . self::genRandomString() . ".com"  . "', ";
            $sql .= "Telephone = '5127870457', ";
            $sql .= "Cellphone = '5555555555', ";
            $sql .= "Website = '" . self::genRandomString() . "', ";

            $sql .= "K12_Clientele_Indicator = 1, ";

            $sql .= "Higher_Education_Clientele_Indicator = 1, ";

            $sql .= "Background_Description = '" . self::genRandomString(200) . "', ";
            $sql .= "Education_Description = '" . self::genRandomString(500) . "', ";
            $sql .= "Experience_Description = '" . self::genRandomString(500) . "', ";

            $sql .= "Complaint_Indicator = 1, ";

            $sql .= "Pending_Litigation_Indicator = 1, ";

            $sql .= "None_Indicator = 0, ";

            $sql .= "Profile_Creation_Date = NOW(), ";

            $sql .= "Profile_Last_Modification_Date = NOW(), ";

            $sql .= "Profile_First_Submission_Date = NOW(), ";

            $sql .= "Profile_Last_Submission_Date = NOW(), ";

            $sql .= "Profile_Submitted_Indicator = 1, ";

            $sql .="Account_Creation_Date = NOW(), ";
            $sql .="Account_Last_Modification_Date = NOW()";

            //echo $sql;

            if (!$cnn->execute($sql)) {
                throw new GeneralRegistryException("Invalid Account Information!");
            }

            $cnn->disconnect();

        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }
        
    }

    public static function getServices () {
        $services = array();
        try {
            $cnn = new Connection();
            $sql = "SELECT * FROM SERVICE ORDER BY Service_Name";
            $rs = $cnn->execute($sql);
            $i = 0;
            while ($row = mysql_fetch_assoc($rs)) {
                $services[$i] = new Service($row["Service_Id"], $row["Service_Name"]);
                $i++;
            }
            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }
        return $services;
    }

    public static function getStates () {
        $states = array();
        try {
            $cnn = new Connection();
            $sql = "SELECT * FROM STATE";
            $rs = $cnn->execute($sql);
            $i = 0;
            while ($row = mysql_fetch_assoc($rs)) {
                $states[$i] = new State($row["State_Id"], $row["State_Name"]);
                $i++;
            }
            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }
        return $states;
    }

    public static function getStateName($stateId) {
        $stateName = "";
        try {
            $cnn = new Connection();

            $sql  = "SELECT * FROM STATE ";
            $sql .= "WHERE STATE.State_Id = " . $stateId;

            $rs = $cnn->execute($sql);
            if (mysql_num_rows($rs) != 1) {
                throw new GeneralRegistryException("getStateName Error!!");
            } else {
                while ($row = mysql_fetch_assoc($rs)) {
                    $stateName = $row["State_Name"];
                }
            }
            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }
        return $stateName;
    }

    public static function getServiceName($serviceId) {
        $serviceName = "";
        try {
            $cnn = new Connection();

            $sql  = "SELECT * FROM SERVICE ";
            $sql .= "WHERE SERVICE.Service_Id = " . $serviceId;

            $rs = $cnn->execute($sql);
            if (mysql_num_rows($rs) != 1) {
                throw new GeneralRegistryException("getServiceName Error!!");
            } else {
                while ($row = mysql_fetch_assoc($rs)) {
                    $serviceName = $row["Service_Name"];
                }
            }
            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }
        return $serviceName;
    }

    public static function isEmailUnique($email) {
        $isEmailUnique = true;

        try {
            $cnn = new Connection();
            $sql = "SELECT Email FROM CONSULTANT WHERE Email = '" . $email . "'";
            $rs = $cnn->execute($sql);
            
            if (mysql_num_rows($rs) > 0) {
                throw new Exception("DuplicatedEmailException");
                $isEmailUnique = false;
            }

            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }
        return $isEmailUnique;
    }

    public static function isValidLogIn($email, $password) {
        $isValidLogIn = false;

        try {
            $cnn = new Connection();

            $sql = "SELECT * FROM CONSULTANT WHERE Email = '" . $email . "'";
            $rs = $cnn->execute($sql);

            if (mysql_num_rows($rs) == 0) {
                throw new Exception("EmailDoesNotExistException");
            }

            $sql = "SELECT * FROM CONSULTANT WHERE Email = '" . $email . "' AND Password LIKE BINARY '" . $password ."'";
            $rs = $cnn->execute($sql);
            
            if (mysql_num_rows($rs) > 0) {
                $isValidLogIn = true;
            } else {
                throw new Exception("InvalidLoginException");
            }

            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }
        return $isValidLogIn;
    }

    public static function createUserAccount($firstName, $lastName, $email, $password) {
        try {
            $cnn = new Connection();
            $sql = "INSERT INTO CONSULTANT SET ";
            $sql .="First_Name = '" . str_replace("'", "\'", $firstName) . "', ";
            $sql .="Last_Name = '" . str_replace("'", "\'", $lastName) . "', ";
            $sql .="Email = '" . $email . "', ";
            $sql .="Password = '" . $password . "', ";
            $sql .="Account_Type_Id = 1, ";
            $sql .="Account_Creation_Date = NOW(), ";
            $sql .="Account_Last_Modification_Date = NOW()";
            if (!$cnn->execute($sql)) {
                throw new GeneralRegistryException("Invalid Account Information!");
            }
            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }
    }

    public static function getUserFullName($email) {
        $userFullName = "";
        try {
            $cnn = new Connection();
            $sql = "SELECT First_Name, Last_Name FROM CONSULTANT WHERE Email = '" . $email . "'";
            $rs = $cnn->execute($sql);
            if (mysql_num_rows($rs) != 1) {
                throw new GeneralRegistryException("Username Provided is not Unique!");
            } else {
                while ($row = mysql_fetch_assoc($rs)) {
                    $userFullName = $row["First_Name"] . " " . $row["Last_Name"];
                }
            }
            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }

        return $userFullName;
    }

    public static function getConsultant ($email) {
        $consultant = new Consultant();
        try {
            $cnn = new Connection();
            $sql = "SELECT * FROM CONSULTANT WHERE Email = '" . $email . "'";
            $rs = $cnn->execute($sql);
            if (mysql_num_rows($rs) != 1) {
                throw new GeneralRegistryException("EmailDoesNotExistException");
            } else {
                while ($row = mysql_fetch_assoc($rs)) {
                    $consultant->setConsultantId($row["Consultant_Id"]);
                    $consultant->setFirstName($row["First_Name"]);
                    $consultant->setLastName($row["Last_Name"]);
                    $consultant->setEmail($row["Email"]);
                    $consultant->setPassword($row["Password"]);
                    $consultant->setAccountTypeId($row["Account_Type_Id"]);
                    $consultant->setCompanyName($row["Company_Name"]);
                    $consultant->setPhysicalAddress($row["Physical_Address"]);
                    $consultant->setPhysicalCity($row["Physical_City"]);
                    $consultant->setPhysicalStateId($row["Physical_State_Id"]);
                    $consultant->setPhysicalZipCode($row["Physical_Zip_Code"]);
                    $consultant->setPhysicalPlusFour($row["Physical_Plus_Four"]);
                    $consultant->setMailingAddress($row["Mailing_Address"]);
                    $consultant->setMailingCity($row["Mailing_City"]);
                    $consultant->setMailingStateId($row["Mailing_State_Id"]);
                    $consultant->setMailingZipCode($row["Mailing_Zip_Code"]);
                    $consultant->setMailingPlusFour($row["Mailing_Plus_Four"]);
                    $consultant->setTelephone($row["Telephone"]);
                    $consultant->setTelephoneExt($row["Telephone_Ext"]);
                    $consultant->setCellphone($row["Cellphone"]);
                    $consultant->setWebsite($row["Website"]);
                    $consultant->setK12ClienteleIndicator($row["K12_Clientele_Indicator"]);
                    $consultant->setHigherEducationClienteleIndicator($row["Higher_Education_Clientele_Indicator"]);
                    $consultant->setServicesOfferedDescription($row["Services_Offered_Description"]);
                    $consultant->setBackgroundDescription($row["Background_Description"]);
                    $consultant->setEducationDescription($row["Education_Description"]);
                    $consultant->setExperienceDescription($row["Experience_Description"]);
                    $consultant->setComplaintIndicator($row["Complaint_Indicator"]);
                    $consultant->setPendingLitigationIndicator($row["Pending_Litigation_Indicator"]);
                    $consultant->setNoneIndicator($row["None_Indicator"]);
                    $consultant->setAccountCreationDate($row["Account_Creation_Date"]);
                    $consultant->setAccountLastModificationDate($row["Account_Last_Modification_Date"]);
                    $consultant->setProfileCreationDate($row["Profile_Creation_Date"]);
                    $consultant->setProfileLastModificationDate($row["Profile_Last_Modification_Date"]);
                    $consultant->setProfileFirstSubmissionDate($row["Profile_First_Submission_Date"]);
                    $consultant->setProfileLastSubmissionDate($row["Profile_Last_Submission_Date"]);
                    $consultant->setProfileSubmittedIndicator($row["Profile_Submitted_Indicator"]);
                    $consultant->setProfileFirstName($row["Profile_First_Name"]);
                    $consultant->setProfileLastName($row["Profile_Last_Name"]);
                    $consultant->setProfileEmail($row["Profile_Email"]);
                }

                $sql  = "SELECT CONSULTANT_OFFERED_SERVICES.Service_Id, CONSULTANT_OFFERED_SERVICES.Consultant_Id, CONSULTANT_OFFERED_SERVICES.Serving_Since, SERVICE.Service_Name ";
                $sql .= "FROM CONSULTANT_OFFERED_SERVICES, SERVICE ";
                $sql .= "WHERE SERVICE.Service_Id = CONSULTANT_OFFERED_SERVICES.Service_Id ";
                $sql .= "AND Consultant_Id = '" . $consultant->getConsultantId() . "'";
                $rs = $cnn->execute($sql);
                $services = array();
                $i = 0;
                while ($row = mysql_fetch_assoc($rs)) {
                    $tempProvidedService = new ProvidedService($row["Service_Id"], $row["Serving_Since"]);
                    $tempProvidedService->setServiceName($row["Service_Name"]);
                    $services[$i] = $tempProvidedService;
                    $i++;
                }

                $consultant->setServices($services);

            }
            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }

        return $consultant;
    }

    public static function updateUserProfile($consultant) {
        try {

            $isProfileCreated = DBManager::isProfileCreated ($consultant->getEmail());
            $isFirstProfileSubmission = DBManager::isFirstProfileSubmission ($consultant->getEmail());
            $isResubmission = DBManager::isResubmission ($consultant->getEmail(), $consultant->getProfileSubmittedIndicator());

            $cnn = new Connection();
            $queries = array();


            $sqlUpdateConsultantProfile  = "UPDATE CONSULTANT SET ";
            $sqlUpdateConsultantProfile .= "Profile_First_Name = '" . str_replace("'", "\'", $consultant->getProfileFirstName()) . "', ";
            $sqlUpdateConsultantProfile .= "Profile_Last_Name = '" . str_replace("'", "\'", $consultant->getProfileLastName()) . "', ";
            $sqlUpdateConsultantProfile .= "Company_Name = '" . str_replace("'", "\'", $consultant->getCompanyName()) . "', ";
            $sqlUpdateConsultantProfile .= "Physical_Address = '" . str_replace("'", "\'", $consultant->getPhysicalAddress()) . "', ";
            $sqlUpdateConsultantProfile .= "Physical_City = '" . str_replace("'", "\'", $consultant->getPhysicalCity()) . "', ";

            if ($consultant->getPhysicalStateId() == "") {
                $sqlUpdateConsultantProfile .= "Physical_State_Id = NULL, ";
            } else {
                $sqlUpdateConsultantProfile .= "Physical_State_Id = '" . $consultant->getPhysicalStateId() . "', ";
            }

            if ($consultant->getPhysicalZipCode() == "") {
                $sqlUpdateConsultantProfile .= "Physical_Zip_Code = NULL, ";
            } else {
                $sqlUpdateConsultantProfile .= "Physical_Zip_Code = '" . $consultant->getPhysicalZipCode() . "', ";
            }

            if ($consultant->getPhysicalPlusFour() == "") {
                $sqlUpdateConsultantProfile .= "Physical_Plus_Four = NULL, ";
            } else {
                $sqlUpdateConsultantProfile .= "Physical_Plus_Four = '" . $consultant->getPhysicalPlusFour() . "', ";
            }

            $sqlUpdateConsultantProfile .= "Mailing_Address = '" . str_replace("'", "\'", $consultant->getMailingAddress()) . "', ";
            $sqlUpdateConsultantProfile .= "Mailing_City = '" . str_replace("'", "\'", $consultant->getMailingCity()) . "', ";

            if ($consultant->getMailingStateId() == "") {
                $sqlUpdateConsultantProfile .= "Mailing_State_Id = NULL, ";
            } else {
                $sqlUpdateConsultantProfile .= "Mailing_State_Id = '" . $consultant->getMailingStateId() . "', ";
            }

            if ($consultant->getMailingZipCode() == "") {
                $sqlUpdateConsultantProfile .= "Mailing_Zip_Code = NULL, ";
            } else {
                $sqlUpdateConsultantProfile .= "Mailing_Zip_Code = '" . $consultant->getMailingZipCode() . "', ";
            }

            if ($consultant->getMailingPlusFour() == "") {
                $sqlUpdateConsultantProfile .= "Mailing_Plus_Four = NULL, ";
            } else {
                $sqlUpdateConsultantProfile .= "Mailing_Plus_Four = '" . $consultant->getMailingPlusFour() . "', ";
            }

            $sqlUpdateConsultantProfile .= "Profile_Email = '" . $consultant->getProfileEmail() . "', ";
            $sqlUpdateConsultantProfile .= "Telephone = '" . $consultant->getTelephone() . "', ";
            $sqlUpdateConsultantProfile .= "Telephone_Ext = '" . $consultant->getTelephoneExt() . "', ";
            $sqlUpdateConsultantProfile .= "Cellphone = '" . $consultant->getCellphone() . "', ";
            $sqlUpdateConsultantProfile .= "Website = '" . $consultant->getWebsite() . "', ";

            if ($consultant->getK12ClienteleIndicator() == "") {
                $sqlUpdateConsultantProfile .= "K12_Clientele_Indicator = NULL, ";
            } else {
                $sqlUpdateConsultantProfile .= "K12_Clientele_Indicator = 1, ";
            }

            if ($consultant->getHigherEducationClienteleIndicator() == "") {
                $sqlUpdateConsultantProfile .= "Higher_Education_Clientele_Indicator = NULL, ";
            } else {
                $sqlUpdateConsultantProfile .= "Higher_Education_Clientele_Indicator = 1, ";
            }
            $sqlUpdateConsultantProfile .= "Services_Offered_Description = '" . str_replace("'", "\'", $consultant->getServicesOfferedDescription()) . "', ";
            $sqlUpdateConsultantProfile .= "Background_Description = '" . str_replace("'", "\'", $consultant->getBackgroundDescription()) . "', ";
            $sqlUpdateConsultantProfile .= "Education_Description = '" . str_replace("'", "\'", $consultant->getEducationDescription()) . "', ";
            $sqlUpdateConsultantProfile .= "Experience_Description = '" . str_replace("'", "\'", $consultant->getExperienceDescription()) . "', ";

            if ($consultant->getComplaintIndicator() == "") {
                $sqlUpdateConsultantProfile .= "Complaint_Indicator = NULL, ";
            } else {
                $sqlUpdateConsultantProfile .= "Complaint_Indicator = 1, ";
            }

            if ($consultant->getPendingLitigationIndicator() == "") {
                $sqlUpdateConsultantProfile .= "Pending_Litigation_Indicator = NULL, ";
            } else {
                $sqlUpdateConsultantProfile .= "Pending_Litigation_Indicator = 1, ";
            }

            if ($consultant->getNoneIndicator() == "") {
                $sqlUpdateConsultantProfile .= "None_Indicator = NULL, ";
            } else {
                $sqlUpdateConsultantProfile .= "None_Indicator = 1, ";
            }

            if (!$isProfileCreated) {
                $sqlUpdateConsultantProfile .= "Profile_Creation_Date = NOW(), ";
            }

            $sqlUpdateConsultantProfile .= "Profile_Last_Modification_Date = NOW(), ";

            if ($isFirstProfileSubmission) {
                $sqlUpdateConsultantProfile .= "Profile_First_Submission_Date = NOW(), ";
            }

            if ($isResubmission) {
                $sqlUpdateConsultantProfile .= "Profile_Last_Submission_Date = NOW(), ";
            }

            if ($consultant->getProfileSubmittedIndicator() == "" || $consultant->getProfileSubmittedIndicator() == "0") {
                $sqlUpdateConsultantProfile .= "Profile_Submitted_Indicator = 0 ";
            } else {
                $sqlUpdateConsultantProfile .= "Profile_Submitted_Indicator = 1 ";
            }

            $sqlUpdateConsultantProfile .= "WHERE Email = '" . $consultant->getEmail() . "'";

            $queries[0] = $sqlUpdateConsultantProfile;

            $sqlDeleteAllConsultantProvidedServices = "DELETE FROM CONSULTANT_OFFERED_SERVICES WHERE Consultant_Id = " . $consultant->getConsultantId();

            $queries[1] = $sqlDeleteAllConsultantProvidedServices;

            $consultantProvidedServices = $consultant->getServices();
            for ($i = 0; $i < count($consultantProvidedServices); $i++) {
                $providedService = $consultantProvidedServices[$i];

                $sqlInsertConsultantProvidedServiceTemp  = "INSERT INTO CONSULTANT_OFFERED_SERVICES SET ";
                $sqlInsertConsultantProvidedServiceTemp .="Consultant_Id = '" . $consultant->getConsultantId() . "', ";
                $sqlInsertConsultantProvidedServiceTemp .="Service_Id = '" . $providedService->getServiceId() . "', ";
                $sqlInsertConsultantProvidedServiceTemp .="Serving_Since = '" . $providedService->getServingSince() . "' ";

                $queries[2 + $i] = $sqlInsertConsultantProvidedServiceTemp;
            }

            if (!$cnn->executeAsATransaction($queries)) {
                throw new GeneralRegistryException("Update User Profile Failed!");
            }

            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }
    }

    private static function isProfileCreated ($email) {
        $isProfileCreated = true;
        try {
            $cnn = new Connection();
            $sql  = "SELECT Email FROM CONSULTANT ";
            $sql .= "WHERE Email = '" . $email . "' AND Profile_Creation_Date is NULL";
            $rs = $cnn->execute($sql);

            if (mysql_num_rows($rs) > 0) {
                $isProfileCreated = false;
            }

            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }
        return $isProfileCreated;
    }

    private static function isFirstProfileSubmission ($email) {
        $isFirstProfileSubmission = false;
        try {
            $cnn = new Connection();
            $sql  = "SELECT Email FROM CONSULTANT ";
            $sql .= "WHERE Email = '" . $email . "' AND Profile_First_Submission_Date is NULL";
            $rs = $cnn->execute($sql);

            if (mysql_num_rows($rs) > 0) {
                $isFirstProfileSubmission = true;
            }

            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }
        return $isFirstProfileSubmission;
    }

    private static function isResubmission ($email, $profileSubmissionIndicator) {
        $isResubmission = false;
        try {
            $cnn = new Connection();
            $sql  = "SELECT Email FROM CONSULTANT ";
            $sql .= "WHERE Email = '" . $email . "' AND ";
            $sql .= "((Profile_Submitted_Indicator = 0 AND Profile_Last_Submission_Date IS NOT NULL) OR (Profile_Last_Submission_Date IS NULL))";
            $rs = $cnn->execute($sql);

            if (mysql_num_rows($rs) > 0 && $profileSubmissionIndicator == 1) {
                $isResubmission = true;
            }

            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }
        return $isResubmission;
    }

    public static function updateUserAccount($consultantId, $oldEmail, $newEmail,
        $newFirstName, $newLastName, $newPassword,
        $oldPassword, $changePasswordIndicator) {
        try {
            if ($oldEmail != $newEmail) {
                if (!DBManager::isEmailUnique($newEmail)) {
                    throw new GeneralRegistryException("DuplicateEmailException");
                }
            }

            $cnn = new Connection();

            $sqlUpdateConsultantAccount  = "UPDATE CONSULTANT SET ";
            $sqlUpdateConsultantAccount .= "First_Name = '" . str_replace("'", "\'", $newFirstName) . "', ";
            $sqlUpdateConsultantAccount .= "Last_Name = '" . str_replace("'", "\'", $newLastName) . "', ";
            $sqlUpdateConsultantAccount .= "Email = '" . $newEmail . "', ";

            if ($changePasswordIndicator == "1") {
                $sqlUpdateConsultantAccount .= "Password = '" . $newPassword . "', ";
            }

            $sqlUpdateConsultantAccount .= "Account_Last_Modification_Date = NOW() ";
            $sqlUpdateConsultantAccount .= "WHERE Consultant_Id = '" . $consultantId . "'";

            if (!$cnn->execute($sqlUpdateConsultantAccount)) {
                throw new GeneralRegistryException("UpdateAccountFailureException");
            }

            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }
    }

    public static function deleteUserAccount($firstName, $lastName, $email, $accountCreationDate) {
        try {

            $cnn = new Connection();
            $queries = array();

            $sqlInsertUserDeletionLog = "INSERT INTO DELETION_LOG SET ";
            $sqlInsertUserDeletionLog .="First_Name = '" . str_replace("'", "\'", $firstName) . "', ";
            $sqlInsertUserDeletionLog .="Last_Name = '" . str_replace("'", "\'", $lastName) . "', ";
            $sqlInsertUserDeletionLog .="Email = '" . $email . "', ";
            $sqlInsertUserDeletionLog .="Account_Creation_Date = '" . $accountCreationDate . "', ";
            $sqlInsertUserDeletionLog .="Account_Deletion_Date = NOW()";

            $queries[0] = $sqlInsertUserDeletionLog;

            $sqlDeleteConsultantAccount  = "DELETE FROM CONSULTANT ";
            $sqlDeleteConsultantAccount .= "WHERE Email = '" . $email . "'";

            $queries[1] = $sqlDeleteConsultantAccount;

            if (!$cnn->executeAsATransaction($queries)) {
                throw new GeneralRegistryException("DeleteAccountFailureException");
            }

            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }
    }

    public static function searchConsultants($searchGroupType, $consultantName,
        $email, $companyName, $servicesList, $pageNumber) {

        $searchResult = new SearchResult();
        try {

            $consultantNameParsed = explode(" ", str_replace("'", "\'", $consultantName));
            $companyNameParsed = explode(" ", str_replace("'", "\'", $companyName));

            $cnn = new Connection();

            $sql  = "SELECT CONSULTANT.Consultant_Id, CONSULTANT.Company_Name, ";
            $sql .= "CONSULTANT.Telephone, CONSULTANT.Website, CONSULTANT.Profile_First_Name, ";
            $sql .= "CONSULTANT.Profile_Last_Name, CONSULTANT.Profile_Email, CONSULTANT.Email ";
            $sql .= "FROM CONSULTANT LEFT JOIN CONSULTANT_OFFERED_SERVICES ON CONSULTANT.Consultant_Id = CONSULTANT_OFFERED_SERVICES.Consultant_Id ";
            $sql .= "WHERE CONSULTANT.Profile_Submitted_Indicator = 1 ";

            if ($searchGroupType == "0") {
                $sql .= "AND CONSULTANT.K12_Clientele_Indicator = 1 ";
            } else if ($searchGroupType == "1") {
                    $sql .= "AND CONSULTANT.Higher_Education_Clientele_Indicator = 1 ";
            }

            for ($i = 0; $i < count($consultantNameParsed); $i++) {
                if ($i == 0) {
                    $sql .= "AND ( CONSULTANT.Profile_First_Name LIKE '%" . $consultantNameParsed[$i] . "%' OR CONSULTANT.Profile_Last_Name LIKE '%" . $consultantNameParsed[$i] . "%' ";
                } else {
                    $sql .= "OR CONSULTANT.Profile_First_Name LIKE '%" . $consultantNameParsed[$i] . "%' OR CONSULTANT.Profile_Last_Name LIKE '%" . $consultantNameParsed[$i] . "%' ";
                }
                if (($i + 1) == count($consultantNameParsed)) {
                    $sql .= ") ";
                }
            }

            if ($email != "") {
                $sql .= "AND CONSULTANT.Profile_Email = '" . $email . "' ";
            }

            for ($i = 0; $i < count($companyNameParsed); $i++) {
                if ($i == 0) {
                    $sql .= "AND ( CONSULTANT.Company_Name LIKE '%" . $companyNameParsed[$i] . "%' ";
                } else {
                    $sql .= "OR CONSULTANT.Company_Name LIKE '%" . $companyNameParsed[$i] . "%' ";
                }
                if (($i + 1) == count($companyNameParsed)) {
                    $sql .= ") ";
                }
            }
            /*
            if (count($servicesList) > 0) {
                $sql .= "AND ( 1 <> 1 ";
            } else {
                $sql .= "AND ( 1 = 1 ";
            }
            for ($i = 0; $i < count($servicesList); $i++) {
                if ($servicesList[$i] != "false") {
                    $sql .= "OR CONSULTANT_OFFERED_SERVICES.Service_Id = '" . $servicesList[$i] . "' ";
                }
            }
            $sql .= ") ";
            */

            /*
            if (count($servicesList) > 0) {
                $sql .= "AND ( 1 <> 1 ";
            } else {
                $sql .= "AND ( 1 = 1 ";
            }
            */
            $isFirstServiceCondition = true;
            for ($i = 0; $i < count($servicesList); $i++) {
                if ($servicesList[$i] != "false") {
                    if ($isFirstServiceCondition) {
                        $sql .= "AND ( 1 <> 1 ";
                         $isFirstServiceCondition = false;
                    }
                    $sql .= "OR CONSULTANT_OFFERED_SERVICES.Service_Id = '" . $servicesList[$i] . "' ";
                }
            }
            if (!$isFirstServiceCondition) {
                $sql .= ") ";
            }

            $sql .= "GROUP BY  CONSULTANT.Consultant_Id, CONSULTANT.Company_Name, ";
            $sql .= "CONSULTANT.Telephone, CONSULTANT.Website, CONSULTANT.Profile_First_Name, ";
            $sql .= "CONSULTANT.Profile_Last_Name, CONSULTANT.Profile_Email ";
            $sql .= "ORDER BY CONSULTANT.Profile_Last_Name ASC, CONSULTANT.Profile_First_Name ASC, CONSULTANT.Company_Name ASC ";
            $sql .= "LIMIT " . (($pageNumber) * 20) . ", 20";

            //echo $sql;

            $rs = $cnn->execute($sql);

            $consultants = array();
            $index = 0;
            while ($row = mysql_fetch_assoc($rs)) {
                $tempConsultant = new Consultant();
                $tempConsultant->setConsultantId($row["Consultant_Id"]);
                $tempConsultant->setCompanyName($row["Company_Name"]);
                $tempConsultant->setTelephone($row["Telephone"]);
                $tempConsultant->setWebsite($row["Website"]);
                $tempConsultant->setProfileFirstName($row["Profile_First_Name"]);
                $tempConsultant->setProfileLastName($row["Profile_Last_Name"]);
                $tempConsultant->setProfileEmail($row["Profile_Email"]);
                $tempConsultant->setEmail($row["Email"]);
                $consultants[$index] = $tempConsultant;
                $index++;
            }

            /* START QUERY TO COUNT TOTAL NUMBER OF RESULTS */

            $sql  = "SELECT CONSULTANT.Consultant_Id, CONSULTANT.Company_Name, ";
            $sql .= "CONSULTANT.Telephone, CONSULTANT.Website, CONSULTANT.Profile_First_Name, ";
            $sql .= "CONSULTANT.Profile_Last_Name, CONSULTANT.Profile_Email ";
            $sql .= "FROM CONSULTANT LEFT JOIN CONSULTANT_OFFERED_SERVICES ON CONSULTANT.Consultant_Id = CONSULTANT_OFFERED_SERVICES.Consultant_Id ";
            $sql .= "WHERE CONSULTANT.Profile_Submitted_Indicator = 1 ";

            if ($searchGroupType == "0") {
                $sql .= "AND CONSULTANT.K12_Clientele_Indicator = 1 ";
            } else if ($searchGroupType == "1") {
                    $sql .= "AND CONSULTANT.Higher_Education_Clientele_Indicator = 1 ";
                }

            for ($i = 0; $i < count($consultantNameParsed); $i++) {
                if ($i == 0) {
                    $sql .= "AND ( CONSULTANT.Profile_First_Name LIKE '%" . $consultantNameParsed[$i] . "%' OR CONSULTANT.Profile_Last_Name LIKE '%" . $consultantNameParsed[$i] . "%' ";
                } else {
                    $sql .= "OR CONSULTANT.Profile_First_Name LIKE '%" . $consultantNameParsed[$i] . "%' OR CONSULTANT.Profile_Last_Name LIKE '%" . $consultantNameParsed[$i] . "%' ";
                }
                if (($i + 1) == count($consultantNameParsed)) {
                    $sql .= ") ";
                }
            }

            if ($email != "") {
                $sql .= "AND CONSULTANT.Profile_Email = '" . $email . "' ";
            }

            for ($i = 0; $i < count($companyNameParsed); $i++) {
                if ($i == 0) {
                    $sql .= "AND ( CONSULTANT.Company_Name LIKE '%" . $companyNameParsed[$i] . "%' ";
                } else {
                    $sql .= "OR CONSULTANT.Company_Name LIKE '%" . $companyNameParsed[$i] . "%' ";
                }
                if (($i + 1) == count($companyNameParsed)) {
                    $sql .= ") ";
                }
            }

            /*
            if (count($servicesList) > 0) {
                $sql .= "AND ( 1 <> 1 ";
            } else {
                $sql .= "AND ( 1 = 1 ";
            }
            */
            $isFirstServiceCondition = true;
            for ($i = 0; $i < count($servicesList); $i++) {
                if ($servicesList[$i] != "false") {
                    if ($isFirstServiceCondition) {
                        $sql .= "AND ( 1 <> 1 ";
                         $isFirstServiceCondition = false;
                    }
                    $sql .= "OR CONSULTANT_OFFERED_SERVICES.Service_Id = '" . $servicesList[$i] . "' ";
                }
            }
            if (!$isFirstServiceCondition) {
                $sql .= ") ";
            }

            $sql .= "GROUP BY  CONSULTANT.Consultant_Id, CONSULTANT.Company_Name, ";
            $sql .= "CONSULTANT.Telephone, CONSULTANT.Website, CONSULTANT.Profile_First_Name, ";
            $sql .= "CONSULTANT.Profile_Last_Name, CONSULTANT.Profile_Email ";

            //echo $sql;

            $rs = $cnn->execute($sql);

            $totalNumberResults = mysql_num_rows($rs);

            /* END QUERY TO COUNT TOTAL NUMBER OF RESULTS */

            $searchResult->setConsultants($consultants);
            $searchResult->setPageNumber($pageNumber);
            $searchResult->setTotalNumberResults($totalNumberResults);

            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }

        return $searchResult;

    }

}
?>
